package data;

import business.Pacote;
import business.Utilizador;
import business.Cliente;
import business.Empregado;

import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 *
 * @author Tiago
 */
public class PacotesData extends InterfaceBD {

    public PacotesData() {
        super();
    }

    public void cancelarSubscricaoPacote(int codCliente, int codPacote) throws SQLException {
        String query = "DELETE From PacotesSubscritos where cod_utps="+codCliente+" AND cod_pacoteps= "+codPacote;
        System.out.println(query);
        res= st.executeUpdate(query);
    }

    public void adicionarSubscricaoPacote(int codCliente, int codPacote) throws SQLException {
        String query = "Insert Into PacotesSubscritos Values("+codCliente+","+codPacote+")";
        System.out.println(query);
        res = st.executeUpdate(query);
    }



    public ArrayList<Pacote> consultarPacCliente(int codCliente) throws Exception {
        ArrayList<Pacote> resultado = new ArrayList<Pacote>();
        String query = "SELECT * from Pacote where cod_pacote IN (Select cod_pacoteps from PacotesSubscritos where cod_utps = )"+codCliente;
        System.out.println(query);
        rs = st.executeQuery(query);
        boolean aux = false;
        while(rs.next()){
            aux = true;
            resultado.add(new Pacote(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDouble(4)));
        }

        if(aux == false){

            throw new Exception("Cliente nao tem Pacotes");
        }else{
            return resultado;

        }
    }

    public Pacote getPacoteByCod(int codPacote) throws Exception {
        String query = "Select * from Pacote where cod_pacote="+codPacote;
        System.out.println(query);
        Pacote pac = null;
        rs = st.executeQuery(query);
        boolean aux = false;
        while(rs.next()){
            aux = true;
            pac = new Pacote(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDouble(4));
        }

        if(aux == false){
            throw new Exception("Pacote nao Existe");
        }else{

            return pac;
        }
    }

    public ArrayList<Integer> daCodCanaisPacote(int codPacote) throws Exception {
        ArrayList<Integer> resultado = new ArrayList<Integer>();
        String query = "Select cod_canalcp from ConstPacote where cod_pacotecp="+codPacote;
        System.out.println(query);
         rs = st.executeQuery(query);

        boolean aux = false;
        while(rs.next()){
            aux = true;
            resultado.add(rs.getInt(1));
        }

        if(aux == false){

            throw new Exception("BaseDados sem Pacotes");
        }else{
            return resultado;

        }

    }

    public void addPacote(String nome,String desc,double preco) throws Exception{
        int numpac = getnextCodPac();
        String query = "Insert Into Table Pacote Values("+numpac+",'"+nome+"','"+desc+"',"+preco+")";
        System.out.println(query);
        res = st.executeUpdate(query);
    }

    public void removerPacote(int codPacote) throws Exception{
        String query = "Delete from Pacote where cod_pacote= "+codPacote;
        System.out.println(query);
        res = st.executeUpdate(query);
    }

    public void adicionaCanalPacote(int codPacote,int cod_canal) throws SQLException {
        String query = "Insert Into Table ConstPacote VALUES("+cod_canal+","+codPacote+")";
        System.out.println(query);
        res= st.executeUpdate(query);
    }

    public void removerCanalPacote(int codPacote,int cod_canal) throws SQLException {
        String query = "Delete from ConstPacote where cod_canalcp="+cod_canal+" AND cod_pacotecp= "+codPacote;
        System.out.println(query);
        res = st.executeUpdate(query);
    }

    public void setPrecoPacote(int codPacote,double preco) throws SQLException {
        String query = "UPDATE Pacote Set precop = "+preco+" where cod_pacote = "+codPacote;
        System.out.println(query);
        res = st.executeUpdate(query);
    }

    public ArrayList<Pacote> consultarPacotes() throws Exception {
        ArrayList<Pacote> resultado = new ArrayList<Pacote>();
        String query = "SELECT * from Pacote";
        System.out.println(query);
        rs  = st.executeQuery(query);

        boolean aux = false;
        while(rs.next()){
            aux = true;
            resultado.add(new Pacote(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDouble(4)));
        }

        if(aux == false){

            throw new Exception("BaseDados sem Pacotes");
        }else{
            return resultado;

        }
    }



    public int getnextCodPac() throws SQLException{

        ResultSet rs;
        int num = 0;

        try {
            rs = st.executeQuery("SELECT MAX(cod_pacote) FROM Pacote");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

            //
        }
        return(num+1);
    }



}
